USE [fsBudget]
GO
/**********************
***** DROP TABLES *****
**********************/
PRINT '***** Dropping Tables *****'
--PRINT ''
/****** Object:  Table [dbo].[Spendings]    Script Date: 03/04/2010 10:55:26 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Spendings]') AND type in (N'U'))
BEGIN
	PRINT 'Dropping Table [dbo].[Spendings]'

	IF  EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_Spendings_MthCategories]') AND parent_object_id = OBJECT_ID(N'[dbo].[Spendings]'))
	BEGIN
		ALTER TABLE [dbo].[Spendings] DROP CONSTRAINT [FK_Spendings_MthCategories]
		PRINT '    Dropped Constraint [FK_Spendings_MthCategories]'
	END

	DROP TABLE [dbo].[Spendings]

	PRINT '    Dropped Table [dbo].[Spendings]'
END
GO

/****** Object:  Table [dbo].[MthCategories]    Script Date: 03/04/2010 10:56:31 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[MthCategories]') AND type in (N'U'))
BEGIN
	PRINT 'Dropping Table [dbo].[MthCategories]'

	IF  EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_MthCategories_Categories]') AND parent_object_id = OBJECT_ID(N'[dbo].[MthCategories]'))
	BEGIN
		ALTER TABLE [dbo].[MthCategories] DROP CONSTRAINT [FK_MthCategories_Categories]
		PRINT '    Dropped Constraint [FK_MthCategories_Categories]'
	END
	IF  EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_MthCategories_Mths]') AND parent_object_id = OBJECT_ID(N'[dbo].[MthCategories]'))
	BEGIN
		ALTER TABLE [dbo].[MthCategories] DROP CONSTRAINT [FK_MthCategories_Mths]
		PRINT '    Dropped Constraint [FK_MthCategories_Mths]'
	END

	DROP TABLE [dbo].[MthCategories]
	
	PRINT '    Dropped Table [dbo].[MthCategories]'
END
GO

/****** Object:  Table [dbo].[Mths]    Script Date: 03/04/2010 10:57:10 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Mths]') AND type in (N'U'))
BEGIN
	PRINT 'Dropping Table [dbo].[Mths]'
	
	DROP TABLE [dbo].[Mths]
	
	PRINT '    Dropped Table [dbo].[Mths]'
END
GO

/****** Object:  Table [dbo].[Categories]    Script Date: 03/04/2010 10:57:49 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Categories]') AND type in (N'U'))
BEGIN
	PRINT 'Dropping Table [dbo].[Categories]'
	
	DROP TABLE [dbo].[Categories]
	
	PRINT '    Dropped Table [dbo].[Categories]'
END
GO

/****** Object:  Table [dbo].[Income]    Script Date: 03/04/2010 10:54:06 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Income]') AND type in (N'U'))

BEGIN
	PRINT 'Dropping Table [dbo].[Income]'
	
	DROP TABLE [dbo].[Income]
	
	PRINT '    Dropped Table [dbo].[Income]'
END
GO



/************************
***** CREATE TABLES *****
************************/
PRINT ''
PRINT '***** Creating Tables *****'
/****** Object:  Table [dbo].[Income]    Script Date: 03/04/2010 10:36:44 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

PRINT 'Creating Table [dbo].[Income]'

CREATE TABLE [dbo].[Income](
	[IncomeId] [int] IDENTITY(1,1) NOT NULL,
	[Description] [nvarchar](50) NOT NULL,
	[Amount] [money] NOT NULL,
	[IsActive] [bit] NOT NULL,
	[Timestamp] [datetime] NOT NULL,
	[Version] [timestamp] NOT NULL,
 CONSTRAINT [PK_Income] PRIMARY KEY CLUSTERED 
(
	[IncomeId] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

PRINT '    Created Table [dbo].[Income]'
GO



/****** Object:  Table [dbo].[Categories]    Script Date: 03/04/2010 10:36:12 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

PRINT 'Creating Table [dbo].[Categories]'

CREATE TABLE [dbo].[Categories](
	[CategoryId] [int] IDENTITY(1,1) NOT NULL,
	[CategoryName] [nvarchar](50) NOT NULL,
	[BudgetAmount] [money] NOT NULL,
	[IsActive] [bit] NOT NULL,
	[Timestamp] [datetime] NOT NULL,
	[Version] [timestamp] NOT NULL,
 CONSTRAINT [PK_Categories] PRIMARY KEY CLUSTERED 
(
	[CategoryId] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

PRINT '    Created Table [dbo].[Categories]'
GO

/****** Object:  Table [dbo].[Mths]    Script Date: 03/04/2010 10:37:11 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

PRINT 'Creating Table [dbo].[Mths]'

CREATE TABLE [dbo].[Mths](
	[MthId] [int] IDENTITY(1,1) NOT NULL,
	[Month] [int] NOT NULL,
	[Year] [int] NOT NULL,
	[TotalIncome] [money] NOT NULL,
	[Timestamp] [datetime] NOT NULL,
	[Version] [timestamp] NOT NULL,
 CONSTRAINT [PK_Mths] PRIMARY KEY CLUSTERED 
(
	[MthId] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

PRINT '    Created Table [dbo].[Mths]'
GO

/****** Object:  Table [dbo].[MthCategories]    Script Date: 03/04/2010 10:36:56 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

PRINT 'Creating Table [dbo].[MthCategories]'

CREATE TABLE [dbo].[MthCategories](
	[MthCategoryId] [int] IDENTITY(1,1) NOT NULL,
	[MthId] [int] NOT NULL,
	[CategoryId] [int] NOT NULL,
	[BudgetAmount] [money] NOT NULL,
	[BaseAmount] [money] NOT NULL,
	[Timestamp] [datetime] NOT NULL,
	[Version] [timestamp] NOT NULL,
 CONSTRAINT [PK_MthCategories] PRIMARY KEY CLUSTERED 
(
	[MthCategoryId] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

ALTER TABLE [dbo].[MthCategories]  WITH CHECK ADD  CONSTRAINT [FK_MthCategories_Categories] FOREIGN KEY([CategoryId])
REFERENCES [dbo].[Categories] ([CategoryId])
GO

ALTER TABLE [dbo].[MthCategories] CHECK CONSTRAINT [FK_MthCategories_Categories]
GO

ALTER TABLE [dbo].[MthCategories]  WITH CHECK ADD  CONSTRAINT [FK_MthCategories_Mths] FOREIGN KEY([MthId])
REFERENCES [dbo].[Mths] ([MthId])
GO

ALTER TABLE [dbo].[MthCategories] CHECK CONSTRAINT [FK_MthCategories_Mths]
GO
PRINT '    Created Table [dbo].[MthCategories] with Constraints'

/****** Object:  Table [dbo].[Spendings]    Script Date: 03/04/2010 10:37:19 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

PRINT 'Creating Table [dbo].[Spendings]'

CREATE TABLE [dbo].[Spendings](
	[SpendingId] [int] IDENTITY(1,1) NOT NULL,
	[Description] [nvarchar](200) NOT NULL,
	[DateSpent] [datetime] NOT NULL,
	[Amount] [money] NOT NULL,
	[MthCategoryId] [int] NULL,
	[Timestamp] [datetime] NOT NULL,
	[Version] [timestamp] NOT NULL,
 CONSTRAINT [PK_Spendings] PRIMARY KEY CLUSTERED 
(
	[SpendingId] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

ALTER TABLE [dbo].[Spendings]  WITH CHECK ADD  CONSTRAINT [FK_Spendings_MthCategories] FOREIGN KEY([MthCategoryId])
REFERENCES [dbo].[MthCategories] ([MthCategoryId])
GO

ALTER TABLE [dbo].[Spendings] CHECK CONSTRAINT [FK_Spendings_MthCategories]
GO

PRINT '    Created Table [dbo].[Spendings] with Constraints'